<?php
/**
 * Created by PhpStorm.
 * User: gqc
 * Date: 2017/7/31
 * Time: 10:10
 */
namespace Api\Service\Amazon;
use Home\Service\CommonService;

CONST SALE_NORMAL_SALE = 1;//正常在售
CONST SALE_NORMAL_SALE_INTERNAL = 3;//3正常在售（国内发货）
CONST SALE_NORMAL_SALE_OVERSEA = 4;//4正常在售（海外仓发货）
CONST SALE_NEW_PRODUCT = 5;//新品在售
CONST SALE_WAIT_ON_SALE = 7;//新品待上架（仅创建了Listing）
CONST SALE_OUT_OF_STOCK = 9;//缺货
CONST SALE_CLEAR_STOCK = 11;//清库存
CONST SALE_SHARE_STOCK = 12;//共享库存
CONST SALE_PLAT_LIMIT = 13;//受限
CONST SALE_OFF = 15;//下架
class CrontabService extends CommonService {
    protected $inboundShipmentidPlanModel;
    protected $newProductDefineDay = 90;
    protected $daySaleLimitAve = 0.5;

    public function __construct() {
        $this->inboundShipmentidPlanModel = D('Inbound/Inboundshipmentplan', 'Model');
        $this->accountSellerSkuModel = D('Api/Amazon/AccountSellerSku','Model');
        $this->reportSaleModel = D('Api/Amazon/ReportSale','Model');
        $this->reportReceipts = D('Api/Amazon/ReportReceipts','Model');
        $this->reportInventory = D('Api/Amazon/ReportInventory','Model');

        //FBA库存转移到一张表里
        $this->reportunsuppressedInventoryModel = D('Api/Amazon/ReportUnsuppressedInventory','Model');
        $this->prepareneedsService = D('Inbound/Prepareneeds', 'Service');
    }

    /**
     * @param array $shipmentids
     * @return bool
     * 从亚马逊获取shipmentid已完成状态并更新shipmentid表
     */
    public function updateShipmentidCompleteStatus ($shipmentids = array()) {
        if(empty($shipmentids)) {
            $datas = $this->inboundShipmentidPlanModel->get_field_by_param(
                array(
                    'package_upload_time'=>array('LT', date('Y-m-d H:i:s', strtotime('-5 day'))),
                    'status'=>array('EQ', 60),
                ),
                array('account_id', 'shipmentid')
            );

            if(!empty($datas)) {
                foreach ($datas as $data) {
                    $shipmentids[$data['account_id']][] = $data['shipmentid'];
                }
            }
        }

        if(empty($shipmentids)) return false;

        $needsUpdateShipmentids = array();
        foreach ($shipmentids as $accountId=>$shipmentidArr) {
            $completeShipmentids = D('Amazon\Inbound', 'Service')->listInboundShipments(
                $accountId,
                array(
                    'ShipmentIdList'=>array_unique($shipmentidArr),
                    'ShipmentStatusList'=>array('CLOSED'),
                )
            );

            if(!empty($completeShipmentids[0])) {
                foreach ($completeShipmentids[0] as $completeShipmentid) {
                    $needsUpdateShipmentids[] = $completeShipmentid['ShipmentId'];
                }
            }
        }

        if(!empty($needsUpdateShipmentids)) {
            $this->inboundShipmentidPlanModel->update(
                array('shipmentid'=>array('IN', $needsUpdateShipmentids)),
                array('status'=>70)
            );
        }
    }

    /**
     * @param int $accountId
     * @param array $sellerSku
     * 更新虚拟SKU的销售状态
     */
    public function autoCalculateListingSaleStatus ($accountId = 0, $sellerSku = array()) {
        //新品的界定日期
        $newProductDefineDate = date('Y-m-d', strtotime('-' . $this->newProductDefineDay . ' day'));
        //最终结果集
        $relationProducts = array();

        //取出需要运算的SellerSku
        $options = array();
        $accountId && $options['account_id'] = intval($accountId);
        !empty($sellerSku) && $options['seller_sku'] = array('IN', $sellerSku);
        /*下架、侵权的不做运算*/
        $options['sale_status_id'] = array(
            'NOT IN',
            array(
                SALE_NORMAL_SALE_INTERNAL, SALE_NORMAL_SALE_OVERSEA,
                SALE_PLAT_LIMIT, SALE_OFF, SALE_CLEAR_STOCK, SALE_SHARE_STOCK
            )
        );
        $options['is_used'] = 1;
        $accountSellerSku = $this->accountSellerSkuModel->where($options)->getField("CONCAT(account_id, '-', seller_sku) AS accountSku, id");

        $commonField = "CONCAT(account_id, '-', sku) AS accountSku";


        //取得各个账号站点的实际库存,存在的都是FBA实际库存不为0的
        $lastUpdateTime = $this->reportInventory->order('id desc')->limit(1)->getField('create_time');
        $rangeTime = date('Y-m-d H:i:s', strtotime($lastUpdateTime) - 3600);

        $checkUpdateTime = $this->reportInventory->where(array('create_time' => array('lt', $rangeTime)))->order('id desc')->limit(1)->getField('create_time');
        if((strtotime($lastUpdateTime) - strtotime($checkUpdateTime)) < 3600) {
            echo '单次库存运算超过1小时，接口存在异常，停止状态运算';
            exit;
        }
        $fbaActualInventory = $this->reportInventory->where(array('create_time' => array('gt', $rangeTime)))->getField("CONCAT(account_id, '-', sku) AS accountSku", true);
        !empty($fbaActualInventory) && $fbaActualInventory = array_keys($fbaActualInventory);

        //取得各个账号的首次入库时间
        $options = array();
        $accountId && $options['account_id'] = intval($accountId);
        !empty($sellerSku) && $options['sku'] = array('IN', $sellerSku);
        $accountSkuFirstWareDate = $this->reportReceipts->where($options)->group('account_id, sku')->getField($commonField . ",min(`received_date`) AS `firstWareDate`");

        //取得每个账号每个虚拟SKU的7天日均销量
        $options['purchase_date'] = array( array('gt', date('Y-m-d', strtotime('-10 day'))), array('lt', date('Y-m-d', strtotime('-3 day'))));
        $accountSkuDaySale = $this->reportSaleModel->where($options)->group('account_id, sku')->getField($commonField . ",SUM(quantity)/7 AS daySale");

        //更新状态
        foreach ($accountSellerSku as $_acSelSku => $_selSkuId) {
            $ass = explode('-', $_acSelSku);

            //刷单虚拟SKU过滤
            if(preg_match('/\b[A-z]\d[A-z]\d/', $ass[1]) || strstr($ass[1], 'SHUADAN')) {
                continue;
            }

            if(!isset($accountSkuFirstWareDate[$_acSelSku]['firstWareDate'])) {
                $relationProducts[SALE_WAIT_ON_SALE][] = $_selSkuId['id'];
                continue;
            }

            //FBA实际库存为0的sku为缺货
            if(!in_array($_acSelSku, $fbaActualInventory)) {
                $relationProducts[SALE_OUT_OF_STOCK][] = $_selSkuId['id'];
                continue;
            }

            //虚拟sku首次上架时间60天以内的sku为新品
            if($accountSkuFirstWareDate[$_acSelSku]['firstWareDate'] >= $newProductDefineDate) {
                $relationProducts[SALE_NEW_PRODUCT][] = $_selSkuId['id'];
            } else {
                //虚拟sku上架时长超过60天，日均销量≥0.5pcs的正常销售的老品
                if(isset($accountSkuDaySale[$_acSelSku]) && $accountSkuDaySale[$_acSelSku]['daySale'] >= $this->daySaleLimitAve) {
                        $relationProducts[SALE_NORMAL_SALE][] = $_selSkuId['id'];
                        continue;
                }

                //日均销量<0.5的
                $relationProducts[SALE_CLEAR_STOCK][] = $_selSkuId['id'];
            }
        }

        //更新状态
        foreach ($relationProducts as $saleStatus=>$oneStatusId) {
            $whereUpdate = array();
            $whereUpdate['id'] = array('IN', $oneStatusId);
            $this->accountSellerSkuModel->where($whereUpdate)->save(array('sale_status_id'=>$saleStatus));

            echo $saleStatus.'状态更新成功'.count($oneStatusId)."条\n";
        }
    }

    public function fbaStockMakeTable() {
        $data = array();
        //抓取最近更新时间
        $lastUpdateTime = $this->reportInventory->order('id desc')->limit(1)->getField('create_time');
        $lastUpdateDate = date('Y-m-d', strtotime($lastUpdateTime));

        //从数据库获取最近数据日期
        $calDate = M('api_fba_stock_attr',' ','fbawarehouse')->order('calDate desc')->limit(1)->getField('calDate');

        if(!empty($calDate) && $calDate >= $lastUpdateDate) {
            echo date('Y-m-d H:i:s') . '亚马逊实际库存抓取数据没有更新' . "\n";
            exit;
        }

        $sql = 'SELECT `ari`.`account_id`, `ari`.`sku`,`aass`.`site_id`,`aass`.`private_sku`,`ari`.`asin`,`ari`.`quantity_for_local_fulfillment` 
        FROM `api_report_inventory` as `ari` 
        LEFT JOIN `api_account_seller_sku` as `aass` 
        ON `ari`.`account_id` = `aass`.`account_id` AND `ari`.`sku` = `aass`.`seller_sku` 
        WHERE `ari`.`create_time` LIKE "' . $lastUpdateDate . '%"';

        $temp = $this->reportInventory->query($sql);

        foreach ($temp as $_inventory) {
            if(!isset($data[$_inventory['account_id']])) {
                //$data[$_inventory['account_id']]['accountId']       = $_inventory['account_id'];
                $data[$_inventory['account_id']]['siteId']          = $_inventory['site_id'];
                $data[$_inventory['account_id']]['localSumQty']     = $_inventory['quantity_for_local_fulfillment'];
                $data[$_inventory['account_id']]['workingSumQty']   = 0;
                $data[$_inventory['account_id']]['shippedSumQty']   = 0;
                $data[$_inventory['account_id']]['receivingSumQty'] = 0;
            } else {
                $data[$_inventory['account_id']]['localSumQty'] += $_inventory['quantity_for_local_fulfillment'];
            }

            //$data[$_inventory['account_id']]['data'][$_inventory['sku']]['sellerSku']  = $_inventory['sku'];
            $data[$_inventory['account_id']]['data'][$_inventory['sku']]['privateSku'] = $_inventory['private_sku'];
            $data[$_inventory['account_id']]['data'][$_inventory['sku']]['asin']       = $_inventory['asin'];
            $data[$_inventory['account_id']]['data'][$_inventory['sku']]['localQty']   = $_inventory['quantity_for_local_fulfillment'];
        }

        $sql = 'SELECT `ari`.`account_id`, `ari`.`sku`, `aass`.`site_id`, `aass`.`private_sku`, `ari`.`asin`, 
            `ari`.`afn_inbound_working_quantity`, `ari`.`afn_inbound_shipped_quantity`, `ari`.`afn_inbound_receiving_quantity` 
        FROM `api_report_unsuppressed_inventory` AS `ari` 
        LEFT JOIN `api_account_seller_sku` AS `aass` 
        ON `ari`.`account_id` = `aass`.`account_id` AND `ari`.`sku` = `aass`.`seller_sku` 
        WHERE `ari`.`create_time` LIKE "' . $lastUpdateDate . '%"';

        $temp = $this->reportunsuppressedInventoryModel->query($sql);
        if(empty($temp)) {
            echo date('Y-m-d H:i:s') . '亚马逊在途库存抓取数据没有更新' . "\n";
            exit;
        }
        foreach ($temp as $_uns) {
            if(!isset($data[$_uns['account_id']])) {
                //$data[$_uns['account_id']]['accountId']       = $_uns['account_id'];
                $data[$_uns['account_id']]['siteId']          = $_uns['site_id'];
                $data[$_uns['account_id']]['localSumQty']     = 0;
                $data[$_uns['account_id']]['workingSumQty']   = $_uns['afn_inbound_working_quantity'];
                $data[$_uns['account_id']]['shippedSumQty']   = $_uns['afn_inbound_shipped_quantity'];
                $data[$_uns['account_id']]['receivingSumQty'] = $_uns['afn_inbound_receiving_quantity'];
            } else {
                $data[$_uns['account_id']]['workingSumQty']   += $_uns['afn_inbound_working_quantity'];
                $data[$_uns['account_id']]['shippedSumQty']   += $_uns['afn_inbound_shipped_quantity'];
                $data[$_uns['account_id']]['receivingSumQty'] += $_uns['afn_inbound_receiving_quantity'];
            }

            if(!isset($data[$_uns['account_id']]['data'][$_uns['sku']])) {
                //$data[$_uns['account_id']]['data'][$_uns['sku']]['sellerSku']  = $_uns['sku'];
                $data[$_uns['account_id']]['data'][$_uns['sku']]['privateSku'] = $_uns['private_sku'];
                $data[$_uns['account_id']]['data'][$_uns['sku']]['asin']       = $_uns['asin'];
                $data[$_uns['account_id']]['data'][$_uns['sku']]['localQty']   = 0;
            }

            $data[$_uns['account_id']]['data'][$_uns['sku']]['workingQty']    = $_uns['afn_inbound_working_quantity'];
            $data[$_uns['account_id']]['data'][$_uns['sku']]['shippedQty']    = $_uns['afn_inbound_shipped_quantity'];
            $data[$_uns['account_id']]['data'][$_uns['sku']]['receivingQty']  = $_uns['afn_inbound_receiving_quantity'];
        }

        foreach ($data as $acId=>$acInfo) {
            $sql = 'INSERT INTO `api_fba_stock_attr`(
                `accountId`, `siteId`, `calDate`, `localSumQty`, `workingSumQty`, `shippedSumQty`, `receivingSumQty`
            ) VALUES (';
            $sql .= "{$acId}, '{$acInfo['siteId']}','{$lastUpdateDate}', '{$acInfo['localSumQty']}',
            '{$acInfo['workingSumQty']}','{$acInfo['shippedSumQty']}','{$acInfo['receivingSumQty']}')";

            M('api_fba_stock_attr',' ','fbawarehouse')->execute($sql);
            foreach ($acInfo['data'] as $sellerSku=>$_sku) {
                $sql = 'INSERT INTO `api_fba_stock_data`(`accountId`, `calDate`, `siteId`, `sellerSku`, `asin`, 
                    `privateSku`, `localQty`, `workingQty`, `shippedQty`, `receivingQty`) VALUES (';
                $sql .= "{$acId}, '{$lastUpdateDate}', '{$acInfo['siteId']}', '{$sellerSku}','{$_sku['asin']}',
                '{$_sku['privateSku']}','{$_sku['localQty']}','{$_sku['workingQty']}','{$_sku['shippedQty']}','{$_sku['receivingQty']}')";

                if($_GET['danger'] && $_GET['danger'] == 'do') {
                    $sql .= " ON DUPLICATE KEY UPDATE `asin` = '{$_sku['asin']}', `localQty` = '{$_sku['localQty']}',
                    `workingQty` = '{$_sku['workingQty']}',`shippedQty` = '{$_sku['shippedQty']}',`receivingQty` = '{$_sku['receivingQty']}'";
                }

                M('api_fba_stock_data',' ','fbawarehouse')->execute($sql);
            }
        }

        echo date('Y-m-d H:i:s') . '更新完毕' . "\n";
        exit;
    }
}